﻿namespace GenericHtml
{
    using System;
    using System.Diagnostics;
    using System.Text;

    [DebuggerDisplay("{Buffer}")]
    public abstract class SqlBuilder
    {
        protected StringBuilder buffer = new StringBuilder();

        /// <summary>
        /// SQL语句缓冲区
        /// </summary>
        public StringBuilder Buffer
        {
            get { return buffer; }
        }

        /// <summary>
        /// 最后一次操作的符号
        /// </summary>
        protected SqlClause LastClause { get; set; }

        /// <summary>
        /// 最后一次操作的闭合符号
        /// </summary>
        protected string EndClause { get; set; }

        /// <summary>
        /// 构建一个SQL语句生成工具对象
        /// </summary>
        public SqlBuilder()
        {
        }

        public SqlBuilder(string query, SqlClause lastClause)
        {
            this.buffer.Append(query);
            this.LastClause = lastClause;
        }

        public virtual void Clear()
        {
            this.LastClause = null;
            this.EndClause = null;
            this.buffer.Clear();
        }

        protected virtual void End()
        {
            if (EndClause != null)
            {
                this.buffer.Append(EndClause);
                this.EndClause = null;
                this.LastClause = MSSqlClauses.Empty;
            }
        }

        protected virtual SqlBuilder AppendClause(
            SqlClause clause,
            string format,
            params object[] args)
        {
            // 如果上一个符号与当前符号相同
            if (clause == this.LastClause)
            {
                // 追加内容
                this.buffer.Append(clause.Separator);
                if (args != null && args.Length != 0)
                {
                    this.buffer.Append(args[0]);
                    for (int i = 1; i < args.Length; i++)
                    {
                        if (clause.Separator != null)
                            this.buffer.Append(clause.Separator);

                        this.buffer.Append(args[i]);
                    }
                }
            }
            else
            {
                // 新操作符
                // 非首次执行则, 则新起一行
                // 如果可能，对上次符号进行闭合
                if (LastClause != null)
                {
                    this.End();
                    this.buffer.Append(Environment.NewLine);
                }

                // 新内容
                this.buffer.Append(clause.Name);
                this.buffer.Append(' ');
                this.buffer.Append(format);
                if (args != null && args.Length != 0)
                {
                    this.buffer.Append(args[0]);
                    for (int i = 1; i < args.Length; i++)
                    {
                        if (clause.Separator != null)
                            this.buffer.Append(clause.Separator);

                        this.buffer.Append(args[i]);
                    }
                }

                this.LastClause = clause;
                this.EndClause = clause.EndFormat;
            }

            return this;
        }

        #region override
        public override string ToString()
        {
            this.End();
            return buffer.ToString();
        }

        public override int GetHashCode()
        {
            return buffer.GetHashCode();
        }

        public override bool Equals(object obj)
        {
            return ((SqlBuilder)obj).buffer.Equals(buffer);
        }

        #endregion

        #region Insert

        /// <summary>
        /// INSERT INTO TABLE(COLUMNS...)
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="columns">字段列表</param>
        /// <returns>返回当前对象</returns>
        public abstract SqlBuilder InsertInto(string table, params string[] columns);

        /// <summary>
        /// VALUES(...)
        /// </summary>
        /// <param name="args">the list of values</param>
        /// <returns>current object</returns>
        public abstract SqlBuilder Values(params string[] args);

        #endregion

        #region Update

        /// <summary>
        /// UPDATE TABLE
        /// </summary>
        /// <param name="table">table's name</param>
        /// <returns>current object</returns>
        public abstract SqlBuilder Update(string table);

        /// <summary>
        /// the clause of update statement
        /// SET column = value, column = value ...
        /// </summary>
        /// <param name="args">the list of 'column = value'</param>
        /// <returns>current object</returns>
        public abstract SqlBuilder Set(params string[] args);

        #endregion

        #region Delete

        /// <summary>
        /// DELETE Table
        /// </summary>
        /// <param name="table">table's name</param>
        /// <returns>current object</returns>
        public abstract SqlBuilder Delete(string table);

        #endregion

        #region Select

        public abstract SqlBuilder Select(params string[] columns);

        public abstract SqlBuilder From(string table, string name = null);

        /// <summary>
        /// where statement
        /// WHERE condition
        /// </summary>
        /// <param name="condition">the condition of 'select/delete/update' statement</param>
        /// <returns>current object</returns>
        public abstract SqlBuilder Where(string condition);

        /// <summary>
        /// 生成语句：
        /// WHERE ({condition}) {contact} ({condition})
        /// </summary>
        /// <param name="contact">拼接所有条件的字符串</param>
        /// <param name="args">the list of conditions</param>
        /// <returns>current object</returns>
        public abstract SqlBuilder Where(string contact, params string[] args);
        public abstract SqlBuilder OrderByAsc(params string[] columns);

        public abstract SqlBuilder OrderByDesc(params string[] columns);

        public abstract SqlBuilder GroupBy(params string[] columns);

        public abstract SqlBuilder Having(string condition);

        public abstract SqlBuilder Having(string contact, params string[] args);

        public abstract SqlBuilder InnerJoin(string table);

        public abstract SqlBuilder InnerJoin(SqlBuilder subQuery, string name);

        public abstract SqlBuilder LeftJoin(string table);

        public abstract SqlBuilder LeftJoin(SqlBuilder subQuery, string name);

        public abstract SqlBuilder FullJoin(string table);

        public abstract SqlBuilder FullJoin(SqlBuilder subQuery, string name);

        public abstract SqlBuilder On(string condition);

        public abstract SqlBuilder On(string contact, params string[] args);
        #endregion

        public virtual SqlBuilder AppendQuery(SqlBuilder builder)
        {
            this.End();
            this.buffer.Append(builder.ToString());
            return this;
        }

        public virtual SqlBuilder AppendQuery(string query)
        {
            this.buffer.Append(query);
            return this;
        }

        public abstract SqlBuilder CreateNestedQuery(params string[] columns);

        public string Escape(string input)
        {
            return System.Web.HttpUtility.HtmlEncode(input);
        }
    }

    /// <summary>
    /// 标示SQL语法符号
    /// </summary>
    public class SqlClause
    {
        private string name;
        private string separator;
        private string startFormat;
        private string endFormat;

        /// <summary>
        /// Gets the name of the clause (eg. SELECT).
        /// </summary>
        public string Name { get { return name; } }

        /// <summary>
        /// Gets the string used to separate the clause body segments,
        /// tipically equals to ', '.
        /// </summary>
        public string Separator { get { return separator; } }

        public string StartFormat { get { return startFormat; } }

        public string EndFormat { get { return endFormat; } }

        public SqlClause(
            string name,
            string separator = null,
            string start = null,
            string end = null)
        {
            this.name = name;
            this.separator = separator;
            this.startFormat = start;
            this.endFormat = end;
        }
    }

    /// <summary>
    /// 用于构建生成SQL语句的基础类
    /// </summary>
    [DebuggerDisplay("{Buffer}")]
    public class MSSqlBuilder : SqlBuilder
    {
        /// <summary>
        /// 构建一个SQL语句生成工具对象
        /// </summary>
        public MSSqlBuilder()
        {
        }

        public MSSqlBuilder(string query)
            : this(query, MSSqlClauses.Empty)
        {
        }

        public MSSqlBuilder(string query, SqlClause lastClause)
            : base(query, lastClause)
        {
        }

        #region Insert

        /// <summary>
        /// INSERT INTO TABLE(COLUMNS...)
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="columns">字段列表</param>
        /// <returns>返回当前对象</returns>
        public override SqlBuilder InsertInto(string table, params string[] columns)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.InsertInto,
                string.Format(MSSqlClauses.InsertInto.StartFormat, table),
                columns);
        }

        /// <summary>
        /// VALUES(...)
        /// </summary>
        /// <param name="args">the list of values</param>
        /// <returns>current object</returns>
        public override SqlBuilder Values(params string[] args)
        {
            if (args == null || args.Length == 0)
                throw new ArgumentNullException("args");

            StringBuilder values = new StringBuilder();
            values.Append("(");
            values.Append(args[0]);
            for (int i = 1; i < args.Length; i++)
            {
                values.Append(", ");
                values.Append(args[i]);
            }

            values.Append(")");

            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.Values,
                MSSqlClauses.Values.StartFormat,
                values.ToString());
        }

        #endregion

        #region Update

        /// <summary>
        /// UPDATE TABLE
        /// </summary>
        /// <param name="table">table's name</param>
        /// <returns>current object</returns>
        public override SqlBuilder Update(string table)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.Update,
                table);
        }

        /// <summary>
        /// the clause of update statement
        /// SET column = value, column = value ...
        /// </summary>
        /// <param name="args">the list of 'column = value'</param>
        /// <returns>current object</returns>
        public override SqlBuilder Set(params string[] args)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.Set,
                null,
                args);
        }

        #endregion

        #region Delete

        /// <summary>
        /// DELETE Table
        /// </summary>
        /// <param name="table">table's name</param>
        /// <returns>current object</returns>
        public override SqlBuilder Delete(string table)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.Delete,
                table);
        }

        #endregion

        #region Select

        public override SqlBuilder Select(params string[] columns)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.Select,
                null,
                columns);
        }

        public override SqlBuilder From(string table, string name = null)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.From,
                name == null ? table : string.Format("{0} AS {1}", table, name),
                null);
        }

        /// <summary>
        /// where statement
        /// WHERE condition
        /// </summary>
        /// <param name="condition">the condition of 'select/delete/update' statement</param>
        /// <returns>current object</returns>
        public override SqlBuilder Where(string condition)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.Where,
                condition);
        }

        /// <summary>
        /// 生成语句：
        /// WHERE ({condition}) {contact} ({condition})
        /// </summary>
        /// <param name="contact">拼接所有条件的字符串</param>
        /// <param name="args">the list of conditions</param>
        /// <returns>current object</returns>
        public override SqlBuilder Where(string contact, params string[] args)
        {
            if (args == null || args.Length == 0)
                throw new ArgumentNullException("args");

            StringBuilder where = new StringBuilder();
            where.AppendFormat("({0})", args[0]);
            for (int i = 1; i < args.Length; i++)
            {
                where.AppendFormat(" {0} ({1})", contact, args[i]);
            }

            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.Where,
                null,
                where.ToString());

        }

        public override SqlBuilder OrderByAsc(params string[] columns)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.OrderByAsc,
                null,
                columns);

        }

        public override SqlBuilder OrderByDesc(params string[] columns)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.OrderByDesc,
                null,
                columns);

        }

        public override SqlBuilder GroupBy(params string[] columns)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.GroupBy,
                null,
                columns);

        }

        public override SqlBuilder Having(string condition)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.Having,
                condition);

        }

        public override SqlBuilder Having(string contact, params string[] args)
        {
            if (args == null || args.Length == 0)
                throw new ArgumentNullException("args");

            StringBuilder having = new StringBuilder();
            having.AppendFormat("({0})", args[0]);
            for (int i = 1; i < args.Length; i++)
            {
                having.AppendFormat(" {0} ({1})", contact, args[i]);
            }

            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.Having,
                null,
                having.ToString());

        }

        public override SqlBuilder InnerJoin(string table)
        {
            return this.Join(MSSqlClauses.InnerJoin, table);
        }

        public override SqlBuilder InnerJoin(SqlBuilder subQuery, string name)
        {
            return this.Join(MSSqlClauses.InnerJoin, subQuery, name);
        }

        public override SqlBuilder LeftJoin(string table)
        {
            return this.Join(MSSqlClauses.LeftJoin, table);
        }

        public override SqlBuilder LeftJoin(SqlBuilder subQuery, string name)
        {
            return (SqlBuilder)this.Join(MSSqlClauses.LeftJoin, subQuery, name);
        }

        public override SqlBuilder FullJoin(string table)
        {
            return this.Join(MSSqlClauses.FullJoin, table);
        }

        public override SqlBuilder FullJoin(SqlBuilder subQuery, string name)
        {
            return (SqlBuilder)this.Join(MSSqlClauses.FullJoin, subQuery, name);
        }

        public override SqlBuilder On(string condition)
        {
            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.On,
                condition);
        }

        public override SqlBuilder On(string contact, params string[] args)
        {
            if (args == null || args.Length == 0)
                throw new ArgumentNullException("args");

            StringBuilder having = new StringBuilder();
            having.AppendFormat("({0})", args[0]);
            for (int i = 1; i < args.Length; i++)
            {
                having.AppendFormat(" {0} ({1})", contact, args[i]);
            }

            return (SqlBuilder)this.AppendClause(
                MSSqlClauses.On,
                null,
                having.ToString());
        }
        #endregion

        /// <summary>
        /// 将当前语句转换成一条新的 select 语句，返回值该语句的SqlBuilder对象。
        /// 例如 SELECT * FROM (SELECT Id, Name FROM Student)
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public override SqlBuilder CreateNestedQuery(params string[] columns)
        {
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            if (columns == null || columns.Length == 0)
                query.Append("*");
            else
            {
                query.Append(columns[0]);
                for (int i = 1; i < columns.Length; i++)
                {
                    query.Append(", ");
                    query.Append(columns[i]);
                }
            }

            query.Append(" FROM ");
            query.Append(this.ToString());

            return new MSSqlBuilder(query.ToString(), MSSqlClauses.From);
        }

        protected MSSqlBuilder Join(SqlClause clause, string table)
        {
            return (MSSqlBuilder)this.AppendClause(
                clause,
                table);
        }

        protected MSSqlBuilder Join(SqlClause clause, SqlBuilder subQuery, string name)
        {
            if (name == null || name.Length == 0)
                throw new ArgumentNullException("name");

            StringBuilder sb = new StringBuilder();
            sb.Append("(");
            sb.Append(subQuery.ToString());
            sb.Append(") AS ");
            sb.Append(name);

            return (MSSqlBuilder)this.AppendClause(
                clause,
                sb.ToString());
        }
    }

    /// <summary>
    /// 默认的语法符号
    /// </summary>
    public class MSSqlClauses
    {
        public static readonly SqlClause Empty = new SqlClause(null, " ");

        public static readonly SqlClause InsertInto = new SqlClause("INSERT INTO", ", ", "{0}(", ")");

        public static readonly SqlClause Values = new SqlClause("VALUES", "," + Environment.NewLine);

        public static readonly SqlClause Update = new SqlClause("UPDATE");

        public static readonly SqlClause Set = new SqlClause("SET", ", ");

        public static readonly SqlClause Delete = new SqlClause("DELETE FROM", " ");

        public static readonly SqlClause Where = new SqlClause("WHERE");

        public static readonly SqlClause Select = new SqlClause("SELECT", ", ");

        public static readonly SqlClause From = new SqlClause("FROM");

        public static readonly SqlClause OrderByAsc = new SqlClause("ORDER BY", ", ", null, " ASC");

        public static readonly SqlClause OrderByDesc = new SqlClause("ORDER BY", ", ", null, " DESC");

        public static readonly SqlClause GroupBy = new SqlClause("GROUP BY", ", ");

        public static readonly SqlClause Having = new SqlClause("Having");

        public static readonly SqlClause InnerJoin = new SqlClause("INNER JOIN");

        public static readonly SqlClause LeftJoin = new SqlClause("LEFT JOIN");

        public static readonly SqlClause FullJoin = new SqlClause("FULL JOIN");

        public static readonly SqlClause On = new SqlClause("ON");

        public static readonly SqlClause Union = new SqlClause("UNION");
    }
}